﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Web;

namespace Tools
{
    public partial class Tool
    {

        /*
         
        ///导出到 EXCEL 
        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)Cache["DS"];
            dt.DefaultView.RowFilter=ViewState["RowFilter"].ToString();
            //复制数据生成一个新的表。
            DataTable dtNew= dt.DefaultView.ToTable();
            string[] CNameList = { "日期", "卖出商", "返点", "买入商", "返点", "亏点", "预定PNR", "出票PNR", "大PNR", "乘客姓名", "乘机人数", "起始地", "目地的", "承运人", "航班号", "舱位", "折扣", "单张票面价", "票面总价", "单张机建税", "总机建税", "起飞时间", "票号", "卖出商订单号","实际收入", "报表收入", "收款帐号", "买入商订单号", "实际支出", "报表支出", "付款帐号", "利润", "操作人", "供应状态", "采购状态", "供应备注", "采购备注" };
            string[] FieldList = { "DealTime", "sourcePlatName", "sourceRebate", "targetPlatName", "targetRebate", "loseRebate", "sourcePnr", "TkPnr", "sourcebigPnr", "Passengers", "PassengerNum", "Departure", "Destination", "Carrier", "FlightNo", "Cabin", "CabinAgio", "TicketPrice", "TotalTicketPrice", "AirTax", "TotalAirTax", "DepartDate", "TicketNos", "SourceOrderNo", "sourceAmount", "SourceBSPTicketPrice", "sourceAccountAb", "targetOrderNo", "targetAmount", "TargetBSPTicketPrice", "targetAccountAb", "Gain", "GLockMan", "SourceStateDesc", "TargetStateDesc", "sourceRemark", "targetRemark" };
            string[] UnionFields = { "DealTime", "sourcePlatName", "sourceRebate", "sourcePnr", "TkPnr", "sourcebigPnr", "Passengers", "PassengerNum", "Departure", "Destination", "Carrier", "FlightNo", "Cabin", "CabinAgio", "TicketPrice", "TotalTicketPrice", "AirTax", "TotalAirTax", "DepartDate", "TicketNos", "SourceOrderNo", "sourceAmount", "sourceAccountAb", "GLockMan", "sourceRemark", };



            if (dtNew.Rows.Count > 0)
            {
                Fdays.WebApp.PublicMethods.ACCommon.ExportDataTableToHTML(this.Response, "AuditDetail" + DateTime.Now.ToString("yyyy-MM-dd"), dtNew, CNameList, FieldList, "", UnionFields);
            }
            else
            {
                Fdays.Common.Jscript.ClientAlert(this, "未找到数据！");
            }

        }
         
         */




        public static void ExportDataTableToHTML(HttpResponse resp, string fileName, DataTable dtSource, string[] CNameList)
        {
            ExportDataTableToHTML(resp, fileName, dtSource, CNameList, new string[0]);
        }
 

 

        public static void ExportDataTableToHTML(HttpResponse resp, string fileName, DataTable dtSource, string[] CNameList, string[] FieldList)
        {
            ExportDataTableToHTML(resp, fileName, dtSource, CNameList, FieldList, "", new string[] { });
        }

        public static void ExportDataTableToHTML(HttpResponse resp, string fileName, DataTable dtSource, string[] CNameList, string[] FieldList, string TableHeader, string[] UnionFields)
        {
            if (FieldList.Length <= 0)
            {
                FieldList = new string[dtSource.Columns.Count];
                for (int i = 0; i < dtSource.Columns.Count; i++)
                {
                    FieldList[i] = dtSource.Columns[i].ColumnName;
                }
            }

            if (CNameList.Length <= 0)
            {
                CNameList = new string[dtSource.Columns.Count];
                for (int i = 0; i < dtSource.Columns.Count; i++)
                {
                    CNameList[i] = dtSource.Columns[i].ColumnName;
                }
            }



            string HTML = "";
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            resp.ContentType = "application/ms-excel";


            string HTMLHeader = @"
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns:x='urn:schemas-microsoft-com:office:excel'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=gb2312' />
<title></title>
</head>
    <body>
        <table width='100%' border='1' align='center' cellpadding='0' cellspacing='0' bordercolorlight='#FFFFFF' bordercolordark='#CCCCCC'>
";

            StringBuilder HTMLBody = new StringBuilder();
            string HTMLFooter = @"
        </table>
    </body>
</html>";

            HTMLBody.Append(TableHeader);

            string TRHtml = "";
            for (int i = 0; i < CNameList.Length; i++)
            {
                TRHtml += "\t\t<TH>" + CNameList[i] + "</TH>" + "\n";
            }
            HTMLBody.Append("\t<TR>" + "\n" + TRHtml + "\t</TR>" + "\n");


            if (FieldList.Length > 0)
            {
                for (int i = dtSource.Columns.Count - 1; i >= 0; i--)
                {
                    if (!isExistsColumn(dtSource.Columns[i].ColumnName, FieldList))
                    {
                        dtSource.Columns.RemoveAt(i);
                    }
                }
            }
            bool isUnion = false;
            int UnionRowCount = 1;
            DataRow drLast = null;
            //int ReplaceStartIndex = 0;
            StringBuilder HTMLBodySnippet = new StringBuilder();
            foreach (DataRow dr in dtSource.Rows)
            {
                if (SameToLast(dr, drLast, UnionFields))
                {
                    isUnion = true;
                    UnionRowCount++;
                }
                else
                {
                    isUnion = false;
                    //HTMLBody = HTMLBody.Replace("XRowCount", UnionRowCount.ToString(), ReplaceStartIndex, HTMLBody.Length - ReplaceStartIndex);
                    HTMLBodySnippet = HTMLBodySnippet.Replace("XRowCount", UnionRowCount.ToString());
                    HTMLBody.Append(HTMLBodySnippet);
                    HTMLBodySnippet = new StringBuilder();
                    //ReplaceStartIndex = HTMLBody.Length;
                    UnionRowCount = 1;
                }

                TRHtml = "";
                for (int i = 0; i < FieldList.Length; i++)
                {
                    if (!isUnion || !isExistsColumn(FieldList[i], UnionFields))
                    {
                        string fieldValue = dr[FieldList[i]].ToString().Trim();

                        //判断要输出的值如果是数字，即小于八位，则将成数字输出，否则当成字符串。
                        //(全部当字符串输出有问题吗？ 答：如果把数字当字符串输出，则失去了在 Excel当中一选中则自动汇总的功能。 )
                        //如果是诸如 0012 这类的则当成字符输出，0.12 则当数字。
                        if (!IsNumeric(fieldValue) || fieldValue.Length > 10 || ((new Regex(@"^0[^\.]").IsMatch(fieldValue))))
                        {
                            if (isExistsColumn(FieldList[i], UnionFields))
                            {
                                TRHtml += "\t\t<TD  rowspan='XRowCount' x:str>" + fieldValue + "</TD>" + "\n";
                            }
                            else
                            {
                                TRHtml += "\t\t<TD x:str>" + fieldValue + "</TD>" + "\n";
                            }
                        }
                        else
                        {
                            if (isExistsColumn(FieldList[i], UnionFields))
                            {
                                TRHtml += "\t\t<TD rowspan='XRowCount'>" + fieldValue + "</TD>" + "\n";
                            }
                            else
                            {
                                TRHtml += "\t\t<TD>" + fieldValue + "</TD>" + "\n";
                            }
                        }
                    }
                }
                HTMLBodySnippet.Append("\t<TR>" + "\n" + TRHtml + "\t</TR>" + "\n");
                drLast = dr;
            }
            if (HTMLBodySnippet.ToString() != "")
            {
                HTMLBodySnippet = HTMLBodySnippet.Replace("XRowCount", UnionRowCount.ToString());
            }
            HTMLBody = HTMLBody.Append(HTMLBodySnippet);

            HTML = HTMLHeader + HTMLBody.ToString() + HTMLFooter;
            resp.Write(HTML);
            resp.End();

        }


        /// <summary>
        /// 当前行是否与上一行相同
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="drLast"></param>
        /// <param name="UnionFields"></param>
        private static bool SameToLast(DataRow dr, DataRow drLast, string[] UnionFields)
        {
            if (dr == null || drLast == null || UnionFields.Length <= 0)
                return false;
            foreach (string fieldName in UnionFields)
            {
                if (dr[fieldName].ToString().Trim() != drLast[fieldName].ToString().Trim())
                    return false;
            }
            return true;

        }

        #region isExistsColumn
        private static bool isExistsColumn(string colName, string[] fieldList)
        {
            foreach (string field1 in fieldList)
            {
                if (colName.ToUpper() == field1.ToUpper())
                {
                    return true;
                }
            }
            return false;
        }
        #endregion

        /// <summary>
        /// 判断对象是否为Int32类型的数字
        /// </summary>
        /// <param name="Expression"></param>
        /// <returns></returns>
        public static bool IsNumeric(string expression)
        {
            if (expression != null)
            {
                string str = expression;
                if (str.Length > 0 && str.Length <= 11 && Regex.IsMatch(str, @"^[-]?[0-9]*[.]?[0-9]*$"))
                {
                    if ((str.Length < 10) || (str.Length == 10 && str[0] == '1') || (str.Length == 11 && str[0] == '-' && str[1] == '1'))
                    {
                        return true;
                    }
                }
            }
            return false;

        }
    }
}
